#!/usr/bin/perl -w


#=====================================================================
#      A D M I N . P L
#
#       Captivator, a "captive portal" style authentication system.
#
#       Copyright 2005 The University of Wisconsin Board of Regents
#       Licensed and distributed under the terms of the Perl Artistic 
#       License, see doc/LICENSE for details.
#
#       Written by: Dale W. Carder, dwcarder@doit.wisc.edu
#       Network Services Group
#       Division of Information Technology
#       University of Wisconsin at Madison
#
#       For more about Captivator-gw, see:
#       http://net.doit.wisc.edu/~dwcarder/captivator-gw
#
#======================================================================

#======================================================================
#    C O N F I G U R A T I O N   S E C T I O N
#

my $Config = "/usr/local/Captivator/etc/captivator.config";
use lib '/usr/local/Captivator/lib';

#
#======================================================================

#======================================================================
#	U S E   A N D   R E Q U I R E

use CGI qw(:standard);
use CGI::Carp qw(fatalsToBrowser);
use Captivator;

#
#======================================================================


#======================================================================
#	F U N C T I O N   P R O T O T Y P E S

sub setupQuery();
sub parseQueryFields();
sub runQuery();
sub parseQueryOrder();
sub printRow($$$$$$;$;$);

#
#======================================================================



#-----   O K,  A N D  G O !   -----------------------------------


# read in the config file
eval readConfig($Config);


# print http header
print("Content-Type: text/html;\n\n");


# Initalize variables 
my $sqlquery;					# generated dynamicly by this script
my $order_primary = param("order_primary");	# passed in, primary sort order criteria
my $order_secondary = param("order_secondary");	# passed in, secondary sort order criteria
my $pri_desc = param("pri_desc");		# passed in, primary sort ascending or descending
my $sec_desc = param("sec_desc");		# passed in, secondary sort ascending or descending
my $historical_data = param("historical_data");	# passed in, whethor or not we are searching history
my $query_mac  = param("query_mac");		# passed in, mac address query
my $query_user = param("query_user");		# passed in, username query
my $query_ip = param("query_ip");		# passed in, ip address query
my $query_fw = param("query_fw");		# passed in, captivator query

# some sanity / taint checks
if (defined($query_mac) && ($query_mac ne '')) { $query_mac = matchMacAddr($query_mac); }
if (defined($query_ip) && ($query_ip ne '')) { $query_ip = matchIpAddr($query_ip); }

if (defined($query_user) && ($query_user ne '')) {
	# username can only be <= 20 characters
	$query_user = substr($query_user,0,19);
	# usernames can only contain these characters
	my $allowedchars = 'A-Za-z0-9\_\ ';
	$query_user =~ s/[^${allowedchars}]//g;
}


# set up the default query statement
setupQuery();

# call parseQueryFields for sql 'WHERE' search variables
parseQueryFields();

# call parseQueryOrder for sql 'ORDER BY' terms
parseQueryOrder();

# print some html
my $html=<<EOM;
<html>
<head>
 <title>Captivator Admin Interface</title>
</head>

<h2>Captivator Admin Interface</h2>

<hr>
<form name="query" method="get" action="admin.pl">
<table width="80%">

 <td valign="top">
  <b>Search by</b><p>
  <input type="text" name="query_user" value="$query_user" size=16> User name <br>
  <input type="text" name="query_ip" value="$query_ip" size=16> IP Address <br>
  <input type="text" name="query_mac" value="$query_mac" size=16> MAC addr <br>
  <input type="text" name="query_fw" value="$query_fw" size=16> Captivator <br>
 </td>

 <td valign="top">
  <b>Choose a Sort Order</b><p>
  Primary sort order: 
  <select name="order_primary">
EOM
print $html;

# select the right element in the drop down box
print "<option value=\"fwhostname\" ";
if ($order_primary eq "fwhostname") { print " selected "; }
print ">Captivator \n";

print "<option value=\"username\" "; 
if ($order_primary eq "username") { print " selected "; }
print ">Username \n";

print "<option value=\"intvlan\" ";
if ($order_primary eq "intvlan") { print " selected "; }
print ">Vlan \n";

print "<option value=\"logintime\" ";
if ($order_primary eq "logintime") { print " selected "; }
print ">Login time \n";

print " </select><br> \n\n";


# select the right box, based on previous query
print "<input type=\"radio\" name=\"pri_desc\" value=\"ASC\" ";
if ($pri_desc =~ m/ASC/) {print " checked "; }
print "> Ascending \n";

# select the right box, based on previous query
print "<input type=\"radio\" name=\"pri_desc\" value=\"DESC\" ";
if ($pri_desc =~ m/DESC/) {print " checked "; }
print "> Decending \n";

print "<br><br>Secondary sort order:\n <select name=\"order_secondary\"> ";

# select the right element in the drop down box
print "<option value=\"fwhostname\" "; 
if ($order_secondary eq "fwhostname") { print " selected "; }
print ">Captivator \n";

print "<option value=\"username\" ";
if ($order_secondary eq "username") { print " selected "; }
print ">Username \n";

print "<option value=\"intvlan\" ";
if ($order_secondary eq "intvlan") { print " selected "; }
print ">Vlan \n";

print "<option value=\"logintime\" ";
if ($order_secondary eq "logintime") { print " selected "; }
print ">Login time \n";

print " </select><br> \n\n";


# select the right box, based on previous query
print "<input type=\"radio\" name=\"sec_desc\" value=\"ASC\" ";
if ($sec_desc =~ m/ASC/) {print " checked "; }
print "> Ascending \n";

# select the right box, based on previous query
print "<input type=\"radio\" name=\"sec_desc\" value=\"DESC\" ";
if ($sec_desc =~ m/DESC/) {print " checked "; }
print "> Decending \n";


print "<br></td>\n<td valign=\"top\">\n";
print "<b>Choose a Data Set to Search</b><p>";

# select the right box, based on previous query
print "<input type=\"radio\" name=\"historical_data\" value=\"0\" ";
if ($historical_data eq '0') {print " checked "; }
print "> Search currently logged in users \n <br>";

# select the right box, based on previous query
print "<input type=\"radio\" name=\"historical_data\" value=\"1\" ";
if ($historical_data eq '1') {print " checked "; }
print "> Search historical data \n";

$html=<<EOM;

 <br><br>
  <input type="submit" value="Submit"> &nbsp; &nbsp;
  <input type="reset" value="Clear">
 </td>

</table>

</form>

<p>
<hr>
<h2>Query Results</h2>
<font size="-1">$sqlquery</font>

<br><br>

<table border=1 cellspacing=0 cellpadding=5>

<tr><th>User</th><th>Captivator</th><th>Mac Addr</th><th>IP</th><th>Vlan</th><th>Login time</th>

EOM
print $html;

if ($historical_data) {
	print "<th>Logout time</th><th>Logout Reason</th>";
}
print "</tr>\n";

# run the query to print the rows of the tables
runQuery();

print "</table>\n";

#end
exit(1);

##########################################################################



sub setupQuery() {

	# if we are not told to look at historical data, or it is undefined, default is no:
	if (!defined($historical_data) || ($historical_data eq '')) {
        	$historical_data = 0;
	}

	# if we were not passed in a primary ordering, this is the default:
	if (!defined($order_primary) || ($order_primary eq '')) {
        	$order_primary = 'fwhostname';
	}

	# if we were not passed in a secondary ordering, this is the default:
	if (!defined($order_secondary) || ($order_secondary eq '')) {
        	$order_secondary = 'logintime';
	}

        # if we were not passed in a secondary sort, this is the default:
        if (!defined($sec_desc) || ($sec_desc eq '')) {
                $sec_desc = 'DESC';
        }

	# this is the query:
	if ($historical_data) {
        	$sqlquery = "SELECT username,fwhostname,macaddress,INET_NTOA(v4ip),logintime,intvlan,logouttime,logoffreason FROM pastusers";
	} else {
        	$sqlquery = "SELECT username,fwhostname,macaddress,INET_NTOA(v4ip),logintime,intvlan FROM currentusers";
	}

}

sub parseQueryFields() {

	my @query_terms;
	my $yes_we_have_a_where = 0;

	if (defined($query_mac) && ($query_mac ne '')) {
		push(@query_terms, "macaddress = '$query_mac'");
		$yes_we_have_a_where = 1;
	}

	if (defined($query_user) && ($query_user ne '')) {
		push(@query_terms, "username = '$query_user'");
		$yes_we_have_a_where = 1;
	}

	if (defined($query_ip) && ($query_ip ne '')) {
		push(@query_terms, "v4ip = INET_ATON('$query_ip')");
		$yes_we_have_a_where = 1;
	}

	if (defined($query_fw) && ($query_fw ne '')) {
		push(@query_terms, "fwhostname = '$query_fw'");
		$yes_we_have_a_where = 1;
	}

	my $search_str = join(" AND ", @query_terms);

	if ( $yes_we_have_a_where ) {
		$sqlquery = $sqlquery . " WHERE " . $search_str ;
	}
}


sub runQuery() {

	my $dbh = dbConnect();
	if (!$dbh) { # couldn't connect to database
		slog(3,"admin.pl couldn't connect to the database");
	}

	my $sth;

        eval { $sth = $dbh->prepare($sqlquery); } ;
        if ($@) {
                slog(3,"admin.pl couldn't prepare the select sql statement");
                slog(4,$dbh->errstr);
                $sth->finish();
                $dbh->disconnect;
                return(0);
        };

        eval { $sth->execute(); };
        if ($@) {
                slog(3,"admin.pl couldn't run the query");
                slog(4,$sth->errstr);
                slog(6,"Done: expireHosts");
                $sth->finish();
                $dbh->disconnect;
                return(0);
        };

	if ($historical_data) {

        	my ($username,$fwhostname,$macaddress,$v4ip,$logintime,$intvlan,$logofftime,$logoffreason);
        	$sth->bind_columns(\($username,$fwhostname,$macaddress,$v4ip,$logintime,$intvlan,$logofftime,$logoffreason));

        	while($sth->fetch) {
                	printRow($username,$fwhostname,$macaddress,$v4ip,$logintime,$intvlan,$logofftime,$logoffreason);
        	}

	} else {

        	my ($username,$fwhostname,$macaddress,$v4ip,$logintime,$intvlan);
        	$sth->bind_columns(\($username,$fwhostname,$macaddress,$v4ip,$logintime,$intvlan));

        	while($sth->fetch) {
                	printRow($username,$fwhostname,$macaddress,$v4ip,$logintime,$intvlan);
        	}
	}

        return(1);
}


sub parseQueryOrder() {

        # these passed in variables that are gathered from the webpage tell us
        # how we want the data ordered

        # you can order the data by
        # fwhostname
        # username
        # intvlan
        # logintime

        # if 'DESC' is present in the order string, like 'fwhostname DESC'
	# then that item is presented in descending order



	# the reason we do the checks this way, is because we don't want to
	# trust the tainted input from the web page in any way which can
	# lead to sql injection.

	if ($order_primary =~ m/fwhostname/) {
		$sqlquery = $sqlquery . " ORDER BY fwhostname";
	}

	if ($order_primary =~ m/username/) {
                $sqlquery = $sqlquery . " ORDER BY username";
        }

	if ($order_primary =~ m/intvlan/) {
                $sqlquery = $sqlquery . " ORDER BY intvlan";
        }

        if ($order_primary =~ m/logintime/) {
                $sqlquery = $sqlquery . " ORDER BY logintime";
        }

	if ($pri_desc =~ m/DESC/i) {
		$sqlquery = $sqlquery . " DESC";
	}

	if ($order_secondary =~ m/fwhostname/) {
                $sqlquery = $sqlquery . " , fwhostname";
        }

        if ($order_secondary =~ m/username/) {
                $sqlquery = $sqlquery . " , username";
        }

        if ($order_secondary =~ m/intvlan/) {
                $sqlquery = $sqlquery . " , intvlan";
        }

        if ($order_secondary =~ m/logintime/) {
                $sqlquery = $sqlquery . " , logintime";
        }

        if ($sec_desc =~ m/DESC/i) {
                $sqlquery = $sqlquery . " DESC";
        }

	#print $sqlqyery;

}


sub printRow($$$$$$;$;$) {
	# prints a html table row

	my $username = shift;
	my $fwhostname = shift;
	my $macaddress = shift;
	my $v4ip = shift;
	my $logintime = shift;
	my $intvlan = shift;
	my $logofftime = shift;
	my $logoffreason = shift;

	print "<tr>";
	print "<td>$username</td><td>$fwhostname</td><td>$macaddress</td>";
	print "<td>$v4ip</td><td>$intvlan</td><td>$logintime</td>";

	if ($historical_data) {
		print "<td>$logofftime</td><td>$logoffreason</td>";
	}
	print "</tr>\n"
}

